package com.sinopac.model.ccroger;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;

import com.sinopac.entity.ccroger.CcrogerVO;
import com.sinopac.model.core.ModelCore;
import com.sinopac.tableBean.TbCustomers;
import com.sinopac.tableBean.TbTokenCustomer;

public class CcrogerModel extends ModelCore{
	
	private StringBuffer sql = new StringBuffer();
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	
	@SuppressWarnings("unchecked")
	public CcrogerVO doQuery(String custId){
		sql.setLength(0);
		sql.append("select * from Customers ");
		sql.append("where customer_id = :custId ");
		setObject("custId", custId);
		CcrogerVO vo = (CcrogerVO)exeQuery(sql.toString(), CcrogerVO.class).get(0);
		vo.setGroupList(new ArrayList<TbCustomers>());
		sql.setLength(0);
		sql.append("SELECT tc.token_esn, tc.related_to_time, jq.job_id , ");
		sql.append("'token_apply' = ");
		sql.append("case ");
		sql.append("when jq.job_id is null and tc.token_esn is null and c.is_group = '1' and c.is_group_main = '1' then '0' ");
		sql.append("when jq.job_id is null and tc.token_esn is null and c.is_group = '0' then '0' ");
		sql.append("when jq.job_id is not null and tc.token_esn is null then '1' ");
		sql.append("when tc.token_esn is not null then '2' ");
		sql.append("else '3' ");
		sql.append("end ");
		sql.append("FROM customers c LEFT JOIN token_customer tc ");
		sql.append("ON tc.related_to IN ( c.customer_id, c.group_id ) ");
		sql.append("LEFT JOIN job_queues jq ");
		sql.append("ON c.customer_id = jq.customer_id ");
		sql.append("OR c.group_id = jq.group_id ");
		sql.append("where c.customer_id = ? ");
		setObject(0, custId);
		Map<String,Object> tcMap = (Map<String,Object>)exeQuery(sql.toString()).get(0);
		if((String)tcMap.get("token_esn") == null){
			vo.setToken_esn(getToken());
		}else{
			vo.setToken_esn((String)tcMap.get("token_esn"));
		}
//		System.out.println("setToken_esn=" + (String)tcMap.get("Token_esn"));
//		vo.setToken_esn((String)tcMap.get("Token_esn"));
		vo.setToken_apply((String)tcMap.get("token_apply"));
		vo.setRelated_to_time(tcMap.get("related_to_time")==null?"":sdf.format((Timestamp)tcMap.get("related_to_time")));
		if(StringUtils.isNotBlank(vo.getGroupId())){
			sql.setLength(0);
			sql.append("select * from Customers where group_id = ? and customer_id <> ? ");
			super.setObject(0, vo.getGroupId());
			super.setObject(1, custId);
			List<TbCustomers> list = exeQuery(sql.toString(), TbCustomers.class);
			vo.setGroupList(list);
		}
		vo.setOperator(getRecentOperator(custId));
		return vo;
	}
	
	@SuppressWarnings("unchecked")
	public JSONArray getTokenList(String search){
		JSONArray json = new JSONArray();
		sql.setLength(0);
		sql.append("select top 20 t.token_esn ");
		sql.append("from tokens t ");
		sql.append("left join token_customer tc ");
		sql.append("on t.token_esn = tc.token_esn ");
		sql.append("left join job_queues jq ");
		sql.append("on t.token_esn = jq.token_esn ");
		sql.append("where tc.related_to is null ");
		sql.append(" and jq.job_id is null ");
		if(StringUtils.isNotBlank(search)){
			sql.append(" and t.token_esn like ? ");
			setObject(0, search + "%");
		}
		sql.append("order  by t.created_time desc ");
		List<TbTokenCustomer> list = exeQuery(sql.toString(),TbTokenCustomer.class);
		if(!list.isEmpty()){
			json = JSONArray.fromObject(list);
		}
		return json;
	}
	
	public String getToken(){
		String token = "";
		sql.setLength(0);
		sql.append("select top 1 t.token_esn ");
		sql.append("from tokens t ");
		sql.append("left join token_customer tc ");
		sql.append("on t.token_esn = tc.token_esn ");
		sql.append("left join job_queues jq ");
		sql.append("on t.token_esn = jq.token_esn ");
		sql.append("where tc.related_to is null ");
		sql.append(" and jq.job_id is null ");
		
		Map<String,Object> tkMap = (Map<String,Object>)exeQuery(sql.toString()).get(0);
		
		if(tkMap !=null){
			token = (String)tkMap.get("token_esn");
			System.out.println("tokentoken_esn1=" + token);
		}
		System.out.println("tokentoken_esn2=" + token);
		return token;
	}
}
